package com.oa.tools.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

@SuppressWarnings("deprecation")
public class ExcelUtil
{
  public static List<List<String>> readSheetOfExcel(Sheet sheet)
  {
    int rowCnt = sheet.getLastRowNum() + 1;
    if (rowCnt == 1) {
      return null;
    }

    List<List<String>> sheetData = new ArrayList<List<String>>();

    for (int i = 0; i < rowCnt; i++) {
      Row curRow = sheet.getRow(i);
      if (curRow != null)
      {
        int colCnt = curRow.getLastCellNum();
        if (colCnt > 0)
        {
          List<String> rowData = new ArrayList<String>();
          for (int j = 0; j < colCnt; j++) {
            Cell curCell = curRow.getCell(j);
            String cellData = getCellStrVal(curCell);
            rowData.add(cellData);
          }
          sheetData.add(rowData);
        }
      }
    }
    return sheetData;
  }

  public static List<List<String>> readDataFromExcel(String excelPath)
  {
    Sheet[] sheets = getSheetsFromExcel(excelPath);
    if ((sheets != null) && (sheets.length > 0)) {
      return readSheetOfExcel(sheets[0]);
    }
    return null;
  }

  
  public static List<Map<String,String>> readDataFromExcelToMapList(String excelPath)
  {
	 List<Map<String,String>> dataMapList = new ArrayList<Map<String,String>>();
	 
    Sheet[] sheets = getSheetsFromExcel(excelPath);
    if ((sheets != null) && (sheets.length > 0)) {
    	List<List<String>> dataList = readSheetOfExcel(sheets[0]);
    	List<String> titles = dataList.get(0);
 		for(int i=1;i<dataList.size();i++) {
 			List<String> lineDatas = dataList.get(i);
 			Map<String,String> lineMap = new HashMap<String,String>();
 			for(int j=0;j<titles.size();j++) {
 				String title = titles.get(j);
 				lineMap.put(title, lineDatas.get(j));
 			}
 			dataMapList.add(lineMap);
 		}
    }
    return dataMapList;
  }
  
  public static Sheet getSheetByName(String filePath, String sheetName)
  {
    Sheet sheet = null;
    String fileType = filePath.substring(filePath.lastIndexOf("."));
    if ((fileType.equals(".xls")) || (fileType.equals(".xlsx")) || (fileType.equals(".et"))) {
      File file = new File(filePath);
      if (file.isFile()) {
        Workbook workbook = null;
        InputStream fis = null;
        try {
          fis = new FileInputStream(file);
          workbook = ".xlsx".equals(fileType) ? new XSSFWorkbook(fis) : new HSSFWorkbook(fis);
          sheet = workbook.getSheet(sheetName);
        }
        catch (Exception e) {
          throw new RuntimeException(filePath, e);
        } finally {
          try {
            if (fis != null)
              fis.close();
          }
          catch (IOException e)
          {
            e.printStackTrace();
          }
        }
      }
    }

    return sheet;
  }

  public static String getCellStrVal(Cell cell)
  {
    if (cell == null)
      return "";
    if ((cell.getCellType() == 0) && 
      (DateUtil.isCellDateFormatted(cell)))
    {
      Date d = DateUtil.getJavaDate(cell.getNumericCellValue());
      return d.toLocaleString();
    }
    cell.setCellType(1);
    return cell.getStringCellValue().trim();
  }

  public static void writeExcel(String filePath, String sheetName, List<List<String>> datas)
  {
    HSSFWorkbook workbook = new HSSFWorkbook();

    HSSFSheet sheet = workbook.createSheet(sheetName);

    for (int r = 0; r < datas.size(); r++) {
      List<String> rowData = (List<String>)datas.get(r);
      HSSFRow row = sheet.createRow(r);

      for (int c = 0; c < rowData.size(); c++) {
        Cell cell = row.createCell(c);
        cell.setCellValue((String)rowData.get(c));
      }
    }

    OutputStream os = null;
    try {
      os = FileUtil.openOutputStream(FileUtil.newFile(filePath));
      workbook.write(os);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      FileUtil.closeStream(os);
    }
  }

  public static Workbook generWorkbook(Map<String, List<List<String>>> datas)
  {
    HSSFWorkbook workbook = new HSSFWorkbook();
    for (String sheetName : datas.keySet()) {
      List<List<String>> rowDatas = datas.get(sheetName);

      HSSFSheet sheet = workbook.createSheet(sheetName);
      for (int r = 0; r < datas.size(); r++) {
    	List<String> rowData = (List<String>)rowDatas.get(r);
        HSSFRow row = sheet.createRow(r);

        for (int c = 0; c < rowData.size(); c++) {
          Cell cell = row.createCell(c);
          cell.setCellValue((String)rowData.get(c));
        }
      }
    }

    return workbook;
  }

  public static void writeExcel(String filePath, Workbook workbook) {
    OutputStream os = null;
    try {
      os = FileUtil.openOutputStream(FileUtil.newFile(filePath));
      workbook.write(os);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      FileUtil.closeStream(os);
    }
  }

  public static Sheet[] getSheetsFromExcel(String filePath)
  {
    Sheet[] sheets = new Sheet[0];
    String fileType = filePath.substring(filePath.lastIndexOf("."));
    if ((fileType.equals(".xls")) || (fileType.equals(".xlsx")) || (fileType.equals(".et"))) {
      File file = new File(filePath);
      if (file.isFile()) {
        Workbook workbook = null;
        InputStream fis = null;
        try {
          fis = new FileInputStream(file);
          workbook = ".xlsx".equals(fileType) ? new XSSFWorkbook(fis) : new HSSFWorkbook(fis);
          int sheetCnt = workbook.getNumberOfSheets();
          sheets = new Sheet[sheetCnt];
          for (int i = 0; i < sheetCnt; i++)
            sheets[i] = workbook.getSheetAt(i);
        }
        catch (Exception e)
        {
          throw new RuntimeException(filePath, e);
        } finally {
          try {
            if (fis != null)
              fis.close();
          }
          catch (IOException e)
          {
            e.printStackTrace();
          }
        }
      }
      else {
        return null;
      }
    }
    return sheets;
  }

  public static void writeExcel(String filePath, List<List<String>> datas) {
    HSSFWorkbook workbook = new HSSFWorkbook();

    HSSFSheet sheet = workbook.createSheet();

    for (int r = 0; r < datas.size(); r++) {
      List<String> rowData = datas.get(r);
      HSSFRow row = sheet.createRow(r);

      for (int c = 0; c < rowData.size(); c++) {
        Cell cell = row.createCell(c);
        cell.setCellValue((String)rowData.get(c));
      }
    }

    OutputStream os = null;
    try {
      os = FileUtil.openOutputStream(FileUtil.newFile(filePath));
      workbook.write(os);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      FileUtil.closeStream(os);
    }
  }
  
  
  public static void exportExcel(HttpServletResponse response, String fileName, List<List<String>> datas) throws Exception {
	  
      // 告诉浏览器用什么软件可以打开此文件
//      response.setHeader("content-type", "application/vnd.ms-excel");
      
      response.setHeader("content-type", "application/octet-stream");
      response.setContentType("application/octet-stream");
      
      // 下载文件的默认名称
      response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(fileName, "utf-8"));
      
      writeExcel(datas, response.getOutputStream());
  }
  
  
  	public static void writeExcel(List<List<String>> datas,OutputStream os) {
	    HSSFWorkbook workbook = new HSSFWorkbook();
	    HSSFSheet sheet = workbook.createSheet();

	    for (int r = 0; r < datas.size(); r++) {
	      List<String> rowData = datas.get(r);
	      HSSFRow row = sheet.createRow(r);

	      for (int c = 0; c < rowData.size(); c++) {
	        Cell cell = row.createCell(c);
	        cell.setCellValue((String)rowData.get(c));
	      }
	    }

	    try {
	        workbook.write(os);
	      } catch (Exception e) {
	        e.printStackTrace();
	      } finally {
	        FileUtil.closeStream(os);
	      }
	  }

  public static void writeExcel(String filePath, Sheet sheet)
  {
    Workbook workbook = sheet.getWorkbook();

    OutputStream os = null;
    try {
      os = FileUtil.openOutputStream(FileUtil.newFile(filePath));
      workbook.write(os);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      FileUtil.closeStream(os);
    }
  }

  public static void copyCellStyle(CellStyle fromStyle, CellStyle toStyle)
  {
    toStyle.setAlignment(fromStyle.getAlignment());

    toStyle.setBorderBottom(fromStyle.getBorderBottom());
    toStyle.setBorderLeft(fromStyle.getBorderLeft());
    toStyle.setBorderRight(fromStyle.getBorderRight());
    toStyle.setBorderTop(fromStyle.getBorderTop());
    toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
    toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
    toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
    toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());

    toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
    toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());

    toStyle.setDataFormat(fromStyle.getDataFormat());
    toStyle.setFillPattern(fromStyle.getFillPattern());

    toStyle.setHidden(fromStyle.getHidden());
    toStyle.setIndention(fromStyle.getIndention());
    toStyle.setLocked(fromStyle.getLocked());
    toStyle.setRotation(fromStyle.getRotation());
    toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());
    toStyle.setWrapText(fromStyle.getWrapText());
  }

  public static void copyCell(HSSFWorkbook wb, HSSFCell srcCell, HSSFCell distCell, boolean copyValueFlag)
  {
    HSSFCellStyle newstyle = wb.createCellStyle();
    copyCellStyle(srcCell.getCellStyle(), newstyle);

    distCell.setCellStyle(newstyle);

    if (srcCell.getCellComment() != null) {
      distCell.setCellComment(srcCell.getCellComment());
    }

    int srcCellType = srcCell.getCellType();
    distCell.setCellType(srcCellType);
    if (copyValueFlag)
      if (srcCellType == 0) {
        if (HSSFDateUtil.isCellDateFormatted(srcCell))
          distCell.setCellValue(srcCell.getDateCellValue());
        else
          distCell.setCellValue(srcCell.getNumericCellValue());
      }
      else if (srcCellType == 1)
        distCell.setCellValue(srcCell.getRichStringCellValue());
      else if (srcCellType != 3)
      {
        if (srcCellType == 4)
          distCell.setCellValue(srcCell.getBooleanCellValue());
        else if (srcCellType == 5)
          distCell.setCellErrorValue(srcCell.getErrorCellValue());
        else if (srcCellType == 2)
          distCell.setCellFormula(srcCell.getCellFormula());
      }
  }

  public static CellStyle copyCellStyle(Cell srcCell)
  {
    CellStyle newstyle = srcCell.getSheet().getWorkbook().createCellStyle();
    copyCellStyle(srcCell.getCellStyle(), newstyle);
    return newstyle;
  }

  public static void copyRow(HSSFWorkbook wb, HSSFRow fromRow, HSSFRow toRow, boolean copyValueFlag)
  {
    for (Iterator<Cell> cellIt = fromRow.cellIterator(); cellIt.hasNext(); ) {
      HSSFCell tmpCell = (HSSFCell)cellIt.next();
      HSSFCell newCell = toRow.createCell(tmpCell.getCellNum());
      copyCell(wb, tmpCell, newCell, copyValueFlag);
    }
  }

  public static void copySheet(HSSFWorkbook wb, HSSFSheet fromSheet, HSSFSheet toSheet, boolean copyValueFlag)
  {
    mergerRegion(fromSheet, toSheet);
    for (Iterator<Row> rowIt = fromSheet.rowIterator(); rowIt.hasNext(); ) {
      HSSFRow tmpRow = (HSSFRow)rowIt.next();
      HSSFRow newRow = toSheet.createRow(tmpRow.getRowNum());

      copyRow(wb, tmpRow, newRow, copyValueFlag);
    }
  }

  public static void mergerRegion(HSSFSheet fromSheet, HSSFSheet toSheet)
  {
    int sheetMergerCount = fromSheet.getNumMergedRegions();
    for (int i = 0; i < sheetMergerCount; i++) {
      Region mergedRegionAt = fromSheet.getMergedRegionAt(i);
      toSheet.addMergedRegion(mergedRegionAt);
    }
  }
}